package org.share.comm.db;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.share.comm.bean.BeanUtils;
import org.share.comm.config.PropertiesUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库jdbc常用操作方法
 * 
 * @author hhm
 *
 */
public class DBUtils {

	private static final Logger logger = LoggerFactory.getLogger(DBUtils.class);
	
	private static final String configfile = "jdbc";

	/**
	 * 获取jdbc连接
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		String driver = PropertiesUtils.getString(configfile, "driverClassName");
		String url = PropertiesUtils.getString(configfile, "url");
		String username = PropertiesUtils.getString(configfile, "username");
		String password = PropertiesUtils.getString(configfile, "password");
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,username,password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("jdbc connection exception!\n"+e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("jdbc connection exception!\n"+e.getMessage());
		}
		return conn;
	}

	/**
	 * 使用默认dbcp连接池，执行sql语句
	 * 
	 * @param sql
	 * @return <code>true</code> if the first result is a <code>ResultSet</code>
	 *         object; <code>false</code> if it is an update count or there are
	 *         no results
	 * @throws SQLException
	 */
	public static boolean executeSql(String sql) throws SQLException {
		return executeSql(DBPool.getConnection(), sql);
	}

	/**
	 * 指定jdbc连接，执行sql语句
	 * 
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @return <code>true</code> if the first result is a <code>ResultSet</code>
	 *         object; <code>false</code> if it is an update count or there are
	 *         no results
	 * @throws SQLException
	 */
	public static boolean executeSql(Connection conn, String sql) throws SQLException {
		logger.debug("execute sql : " + sql);
		boolean b = false;
		Statement stat = null;
		try {
			stat = conn.createStatement();
			b = stat.execute(sql);
		} catch (SQLException e) {
			throw e;
		} finally {
			close(conn, stat, null);
		}
		return b;
	}

	/**
	 * 使用默认dbcp连接池，指定jdbc连接，执行insert预处理语句，支持多类型参数
	 * 
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @throws SQLException
	 */
	public static boolean executeInsert(String sql, Object... params) throws SQLException {
		return executeInsert(DBPool.getConnection(), sql, params);
	}

	/**
	 * 指定jdbc连接，执行insert预处理语句，支持多类型参数
	 * 
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @throws SQLException
	 */
	public static boolean executeInsert(Connection conn, String sql, Object... params) throws SQLException {
		logger.debug("execute insert sql : " + sql + " | params : " + paramsToString(params));
		return executePrepareSql(conn, sql, params);
	}

	/**
	 * 使用默认dbcp连接池，批量执行sql
	 * 
	 * @param sqls
	 * @throws SQLException
	 * @return an array of update counts containing one element for each command
	 *         in the batch. The elements of the array are ordered according to
	 *         the order in which commands were added to the batch.
	 */
	public static int[] executeBatch(List<String> sqls) throws SQLException {
		return executeBatch(DBPool.getConnection(), sqls);
	}

	/**
	 * 指定jdbc连接，批量执行sql
	 * 
	 * @param conn
	 *            jdbc连接
	 * @param sqls
	 * @throws SQLException
	 * @return an array of update counts containing one element for each command
	 *         in the batch. The elements of the array are ordered according to
	 *         the order in which commands were added to the batch.
	 */
	public static int[] executeBatch(Connection conn, List<String> sqls) throws SQLException {
		logger.debug("execute batch sql size : " + sqls.size());
		int[] rs = null;
		Statement stat = null;
		try {
			stat = conn.createStatement();
			for (String str : sqls) {
				stat.addBatch(str);
			}
			rs = stat.executeBatch();
		} catch (SQLException e) {
			throw e;
		} finally {
			close(conn, stat, null);
		}
		return rs;
	}

	/**
	 * 默认dbcp连接池，执行Update预处理语句
	 * 
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @throws SQLException
	 */
	public static boolean executeUpdate(String sql, Object... params) throws SQLException {
		return executeUpdate(DBPool.getConnection(), sql, params);
	}

	/**
	 * 指定jdbc连接，执行Update预处理语句
	 * 
	 * @param conn
	 *            指定jdbc连接
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @throws SQLException
	 */
	public static boolean executeUpdate(Connection conn, String sql, Object... params) throws SQLException {
		logger.debug("execute update sql : " + sql + " | params : " + paramsToString(params));
		return executePrepareSql(conn, sql, params);
	}

	/**
	 * 默认dbcp连接池，执行Delete预处理语句
	 * 
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @throws SQLException
	 */
	public static boolean executeDelete(String sql, Object... params) throws SQLException {
		return executeDelete(DBPool.getConnection(), sql, params);
	}

	/**
	 * 指定jdbc连接，执行Delete预处理语句
	 * 
	 * @param conn
	 *            指定jdbc连接
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @throws SQLException
	 */
	public static boolean executeDelete(Connection conn, String sql, Object... params) throws SQLException {
		logger.debug("execute delete sql : " + sql + " | params : " + paramsToString(params));
		return executePrepareSql(conn, sql, params);
	}

	/**
	 * 执行预处理语句
	 * 
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private static boolean executePrepareSql(Connection conn, String sql, Object... params) throws SQLException {
		boolean b = false;
		PreparedStatement stat = null;
		try {
			stat = conn.prepareStatement(sql);

			int i = 0;
			for (Object p : params) {
				i++;
				stat.setObject(i, p);
			}

			b = stat.execute();
		} catch (SQLException e) {
			throw e;
		} finally {
			close(conn, stat, null);
		}

		return b;
	}

	////// ***************** 查询 ************************/////

	/**
	 * 指定jdbc连接，执行sql查询,并回调遍历处理结果
	 * 
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param params
	 * @param callback
	 *            DBUtils.QueryCallback.deal(ResultSet)回调方法
	 * @throws SQLException
	 */
	public static void executeQueryCallback(Connection conn, String sql, Object[] params, QueryCallback callback)
			throws SQLException {
		logger.debug("execute query sql : " + sql + " | params : " + paramsToString(params));

		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(sql);

			int i = 0;
			if (params != null) {
				for (Object p : params) {
					i++;
					stat.setObject(i, p);
				}
			}
			rs = stat.executeQuery();

			while (rs.next()) {
				boolean stop = callback.deal(rs);
				if (stop) {
					break;
				}
			}

		} catch (SQLException e) {
			throw e;
		} finally {
			close(conn, stat, null);
		}
	}
	/**
	 * 指定jdbc连接，执行sql查询,并回调遍历处理结果
	 * 
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param callback
	 *            DBUtils.QueryCallback.deal(ResultSet)回调方法
	 * @throws SQLException
	 */
	public static void executeQueryCallback(Connection conn, String sql, QueryCallback callback)
			throws SQLException {
		executeQueryCallback(DBPool.getConnection(), sql, null, callback);
	}

	/**
	 * 默认dbcp数据源，执行sql查询,并回调遍历处理结果
	 * 
	 * @param sql
	 * @param params
	 * @param callback
	 *            DBUtils.QueryCallback.deal(ResultSet)回调方法
	 * @throws SQLException
	 */
	public static void executeQueryCallback(String sql, Object[] params, QueryCallback callback) throws SQLException {
		executeQueryCallback(DBPool.getConnection(), sql, params, callback);
	}
	/**
	 * 默认dbcp数据源，执行sql查询,并回调遍历处理结果
	 * 
	 * @param sql
	 * @param callback
	 *            DBUtils.QueryCallback.deal(ResultSet)回调方法
	 * @throws SQLException
	 */
	public static void executeQueryCallback(String sql, QueryCallback callback) throws SQLException {
		executeQueryCallback(DBPool.getConnection(), sql, null, callback);
	}

	/**
	 * 执行查询遍历回调接口
	 * 
	 * @author hhm
	 *
	 */
	public interface QueryCallback {
		/**
		 * 查询遍历处理回调方法
		 * 
		 * @param rs
		 * @return true 停止遍历 false继续遍历
		 */
		boolean deal(ResultSet rs) throws SQLException;
	}

	/**
	 * 指定jdbc连接，执行预处理查询语句，提供多类型参数
	 * 
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @return
	 * @throws SQLException
	 */
	public static List<Object[]> executeQuery(Connection conn, String sql, Object... params) throws SQLException {
		final List<Object[]> result = new ArrayList<Object[]>();

		executeQueryCallback(conn, sql, params, new QueryCallback() {

			@Override
			public boolean deal(ResultSet rs) throws SQLException {
				int columnCount = rs.getMetaData().getColumnCount();
				Object[] row = new Object[columnCount];
				for (int index = 0; index < columnCount; index++) {
					row[index] = rs.getObject(index + 1);
				}
				result.add(row);
				return false;
			}

		});
		return result;
	}

	/**
	 * 默认dbcp连接池，执行预处理查询语句，提供多类型参数
	 * 
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @return
	 * @throws SQLException
	 */
	public static List<Object[]> executeQuery(String sql, Object... params) throws SQLException {
		return executeQuery(DBPool.getConnection(), sql, params);
	}

	/**
	 * 指定jdbc连接，执行预处理查询语句，返回指定对象list结果集
	 * 
	 * @param <T>
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public static <T> List<T> executeQuery(Connection conn, String sql, Object[] params, final Class<T> clazz)
			throws SQLException {
		logger.debug("execute query sql : " + sql + " | params : " + paramsToString(params) + " | class : "
				+ clazz.getName());

		final List<T> result = new ArrayList<T>();

		executeQueryCallback(conn, sql, params, new QueryCallback() {

			@Override
			public boolean deal(ResultSet rs) throws SQLException {
				T t = null;
				try {
					t = clazz.newInstance();
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				ResultSetMetaData meta = rs.getMetaData();
				int columnCount = meta.getColumnCount();
				for (int index = 0; index < columnCount; index++) {

					String label = meta.getColumnLabel(index + 1);
					Object obj = rs.getObject(index + 1);
					Class<?> type = BeanUtils.getPropertyTypeIgnoreCase(t, label);

					if (type == null || obj == null) {
						continue;
					}

					Object val = obj;
					if (obj != null && !type.equals(obj.getClass())) {
						// 处理oracle中数值类型返回BigDecimal转换问题
						if (obj.getClass().equals(BigDecimal.class)) {
							BigDecimal bd = (BigDecimal) obj;
							if (type.equals(Double.class) || type.equals(double.class)) {
								val = bd.doubleValue();
							} else if (type.equals(Long.class) || type.equals(long.class)) {
								val = bd.longValue();
							} else if (type.equals(Integer.class) || type.equals(int.class)) {
								val = bd.intValue();
							}
						}
					}
					BeanUtils.setPropertyIgnoreCase(t, label, val);
				}
				result.add(t);
				return false;
			}

		});

		return result;
	}

	/**
	 * 默认dbcp连接池，执行预处理查询语句，返回指定对象list结果集
	 * 
	 * @param <T>
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public static <T> List<T> executeQuery(String sql, Object[] params, Class<T> clazz) throws SQLException {
		return executeQuery(DBPool.getConnection(), sql, params, clazz);
	}
	/**
	 * 指定jdbc连接，执行预处理查询语句，返回指定对象list结果集
	 * 
	 * @param <T>
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public static <T> List<T> executeQuery(Connection conn, String sql, Class<T> clazz) throws SQLException {
		return executeQuery(conn, sql, new Object[0], clazz);
	}
	/**
	 * 默认dbcp连接池，执行预处理查询语句，返回指定对象list结果集
	 * 
	 * @param <T>
	 * @param sql
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public static <T> List<T> executeQuery(String sql, Class<T> clazz) throws SQLException {
		return executeQuery(sql, new Object[0], clazz);
	}

	/**
	 * 指定jdbc连接，执行预处理查询语句，返回指定对象list结果集
	 * 
	 * @param <T>
	 * @param conn
	 *            jdbc连接
	 * @param sql
	 * @param params
	 *            对应预处理语句中"?"号,参数顺序对应语句中"?"号顺序
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public static <T> T executeQueryOne(Connection conn, String sql, Object[] params, final Class<T> clazz)
			throws SQLException {
		
		final List<T> ls = new ArrayList<T>();
		
		executeQueryCallback(conn, sql, params, new QueryCallback() {

			@Override
			public boolean deal(ResultSet rs) throws SQLException {
				T t;
				try {
					t = clazz.newInstance();
				} catch (Exception e) {
					e.printStackTrace();
					return true;
				}
				ResultSetMetaData meta = rs.getMetaData();
				int columnCount = meta.getColumnCount();
				for (int index = 0; index < columnCount; index++) {

					String label = meta.getColumnLabel(index + 1);
					Object obj = rs.getObject(index + 1);
					Class<?> type = BeanUtils.getPropertyTypeIgnoreCase(t, label);

					if (type == null || obj == null) {
						continue;
					}

					Object val = obj;
					if (obj != null && !type.equals(obj.getClass())) {
						// 处理oracle中数值类型返回BigDecimal转换问题
						if (obj.getClass().equals(BigDecimal.class)) {
							BigDecimal bd = (BigDecimal) obj;
							if (type.equals(Double.class) || type.equals(double.class)) {
								val = bd.doubleValue();
							} else if (type.equals(Long.class) || type.equals(long.class)) {
								val = bd.longValue();
							} else if (type.equals(Integer.class) || type.equals(int.class)) {
								val = bd.intValue();
							}
						}
					}
					BeanUtils.setPropertyIgnoreCase(t, label, val);
				}
				ls.add(t);
				return true;
			}

		});
		if (ls.size()>0) {
			return ls.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 使用默认dbcp连接池，执行预处理查询语句，返回一行记录
	 * @param sql
	 * @param params
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public static <T> T executeQueryOne(String sql, Object[] params, Class<T> clazz) throws SQLException {
		return executeQueryOne(DBPool.getConnection(), sql, params, clazz);
	}

	/**
	 * 指定jdbc连接，执行预处理查询语句，返回一行记录
	 * @param conn  jdbc连接
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static Object[] executeQueryOne(Connection conn, String sql, Object... params) throws SQLException {
		final List<Object[]> ls = new ArrayList<Object[]>();
		
		executeQueryCallback(conn, sql, params, new QueryCallback() {

			@Override
			public boolean deal(ResultSet rs) throws SQLException {
				ResultSetMetaData meta = rs.getMetaData();
				int columnCount = meta.getColumnCount();
				Object[] row = new Object[columnCount];
				for (int index = 0; index < columnCount; index++) {
					row[index] = rs.getObject(index + 1);
				}

				ls.add(row);
				return true;
			}

		});
		if(ls.size()>0){
			return ls.get(0);
		}else{
			return null;
		}
	}

	/**
	 * 使用默认dbcp连接池，执行预处理查询语句，返回一行记录
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static Object[] executeQueryOne(String sql, Object... params) throws SQLException {
		return executeQueryOne(DBPool.getConnection(), sql, params);
	}
	
	/**
	 * 指定jdbc连接，执行count查询，返回count数
	 * @param conn  jdbc连接
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static long executeQueryCount(Connection conn, String sql,Object... params) throws SQLException {
		Object[] obj = executeQueryOne(conn, sql, params);
		if(obj==null){
			return 0;
		}else{
			return Long.parseLong(String.valueOf(obj[0]));
		}
	}
	
	/**
	 * 使用默认dbcp连接池，执行count查询，返回count数
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static long executeQueryCount(String sql,Object... params) throws SQLException {
		return executeQueryCount(DBPool.getConnection(), sql, params);
	}

	/**
	 * 释放连接资源，Connection，Statement，ResultSet
	 * 
	 * @param conn
	 * @param stat
	 * @param rs
	 * @throws SQLException
	 */
	public static void close(Connection conn, Statement stat, ResultSet rs) throws SQLException {

		if (rs != null) {
			rs.close();
		}
		if (stat != null) {
			stat.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * 打印数组参数
	 * 
	 * @param params
	 * @return
	 */
	private static String paramsToString(Object[] params) {
		String str = "";
		if (params == null || params.length == 0) {
			return "null";
		}
		for (Object p : params) {
			str += String.valueOf(p) + " & ";
		}
		return str;
	}

	/**
	 * 控制台打印查询结果
	 * 
	 * @param result
	 */
	public static void printQueryResult(List<Object[]> result) {
		int rownum = 0;
		for (Object[] objects : result) {
			rownum++;

			System.out.print("num : " + rownum + "  =>\t");
			for (Object object : objects) {
				System.out.print(object + "\t");
			}
			System.out.println();
		}
	}
}
